﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace VietKinsNutrition.Data
{
    public class ClsTblLenthucphammonanchobuaDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        public void Add(int BaoanId, int Thucpham_ThucanId, float Soluong, float Dongia)
        {
            float Dongiathucte = 0;
            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_ADD;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@BaoanId", BaoanId));
            Cmm.Parameters.Add(new OleDbParameter("@Thucpham_ThucanId", Thucpham_ThucanId));
            Cmm.Parameters.Add(new OleDbParameter("@Soluong", Soluong));
            Cmm.Parameters.Add(new OleDbParameter("@Dongia", Dongia));
            Cmm.Parameters.Add(new OleDbParameter("@Dongiathucte", Dongiathucte));

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Del(int BaoanId)
        {
            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_DELETEBYBAOANID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@BaoanId", BaoanId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        public void DeleteById(int Id)
        {
            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_DELETEBYID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void UpdatebySoluongDongia(int Id, float Soluong, float Dongia)
        {
            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_UPDATEBYSOLUONGDONGIA;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            Cmm.Parameters.Add(new OleDbParameter("@Soluong", Soluong));
            Cmm.Parameters.Add(new OleDbParameter("@Dongia", Dongia));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        public void Update(int Id, float Dongiathucte)
        {
            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_UPDATE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            Cmm.Parameters.Add(new OleDbParameter("@Dongiathucte", Dongiathucte));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataSet TblLenthucphammonanchobuaByBaoanId(int BaoanId)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);

            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUA_GETBYBAOANID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@BaoanId", BaoanId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set;

        }

        public DataSet TblLenthucphammonanchobuathucteByBaoanId(int BaoanId)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);

            string NamePrc = QueryString.SPROC_TBLLENTHUCPHAMMONANCHOBUATHUCTE_GETBYBAOANID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@BaoanId", BaoanId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            DataSet _Set = Set.Clone();
            _Set.Tables[0].Columns.Add("Thanhtien", Set.Tables[0].Columns["Dongiathucte"].DataType);
            foreach (DataRow Rw in Set.Tables[0].Rows)
            {
                _Set.Tables[0].Rows.Add(Rw["STT"]
                    , Rw["Id"],
                    Rw["BaoanId"],
                    Rw["Thucpham_ThucanId"],
                    Rw["Tennguyenlieu"],
                    Rw["Soluong"],
                    Rw["Dongiathucte"],
                    Rw["Tendonvitinh"],
                    (float.Parse(Rw["Soluong"].ToString()) *
                    float.Parse(Rw["Dongiathucte"].ToString())
                    ).ToString());
            }

            // Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], Rw["Dongia"], float.Parse(Rw["Soluong"].ToString()) * float.Parse(Rw["Dongia"].ToString()), Rw["tendonvitinh"], Rw["Id"]);
            return _Set;

        }

        public DataTable PrcTblDm_NhapxuathanghoaById(int Thucpham_thucanId, DateTime NgayNX, int TrangthaiNX)
        {
            try
            {
                DataSet Set = new DataSet();
                string NamePrc = QueryString.THOMND_PRCTBLDM_NHAPXUATHANGHOABYID;
                OleDbCommand Cmm = new OleDbCommand();
                Cmm.Parameters.Add(new OleDbParameter("@Thucpham_thucanId", Thucpham_thucanId));
                Cmm.Parameters.Add(new OleDbParameter("@TrangthaiNX", TrangthaiNX));
                Cmm.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
                ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set);
                return Set.Tables[0];
            }
            catch { return null; }
        }


        public DataTable TblBaocaoduongchattrongbuaan(int KhoilopId, int BuaantrongngayId, DateTime Ngay)
        {
            DataSet Set = new DataSet();
            string NamePrc = " SELECT TblThucpham_thucan.Tenthucan, TblThucpham_thucan.Calo, TblThanhphanhoahoc.Tphh_Protid, TblThanhphanhoahoc.Tphh_Lipid, TblThanhphanhoahoc.Tphh_GlucId, Thucphamtheobua.Soluong, TblThucpham_thucan.Nguondongvat " +
"  FROM (TblThucpham_thucan INNER JOIN TblThanhphanhoahoc ON TblThucpham_thucan.Id = TblThanhphanhoahoc.IdThucpham_thucan) INNER JOIN Thucphamtheobua ON TblThucpham_thucan.Id = Thucphamtheobua.Thucpham_ThucanId " +
 " GROUP BY TblThucpham_thucan.Tenthucan, TblThucpham_thucan.Calo, TblThanhphanhoahoc.Tphh_Protid, TblThanhphanhoahoc.Tphh_Lipid, TblThanhphanhoahoc.Tphh_GlucId, Thucphamtheobua.Soluong, TblThucpham_thucan.Nguondongvat, TblThucpham_thucan.Nguondongvat, Thucphamtheobua.Ngay, Thucphamtheobua.Khoilop " +
 " HAVING (((Thucphamtheobua.Ngay)=#" + Ngay.ToShortDateString() + "#) AND ((Thucphamtheobua.Khoilop)=" + KhoilopId.ToString() + "))            ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set);
            return Set.Tables[0];

        }
        public DataSet TblLenthucphammonanthucteByBaoanIdFillDongia(int BaoanId, DateTime NgayNX, int TrangthaiNX)
        {
            try
            {
                DataSet Set = this.TblLenthucphammonanchobuathucteByBaoanId(BaoanId);
                DataTable NewTbl = new DataTable();
                DataColumn Thanhtien = new DataColumn();

                DataSet Reset = new DataSet();
                DataTable Tbl = new DataTable();

                Tbl.Columns.Add("STT", Set.Tables[0].Columns["STT"].DataType);
                Tbl.Columns.Add("Thucpham_thucanId", Set.Tables[0].Columns["Thucpham_thucanId"].DataType);
                Tbl.Columns.Add("BaoanId", Set.Tables[0].Columns["BaoanId"].DataType);
                Tbl.Columns.Add("Tennguyenlieu", Set.Tables[0].Columns["Tennguyenlieu"].DataType);
                Tbl.Columns.Add("Soluong", Set.Tables[0].Columns["Soluong"].DataType);
                Tbl.Columns.Add("Dongiathucte", Set.Tables[0].Columns["Dongiathucte"].DataType);
                Tbl.Columns.Add("thanhtien", Set.Tables[0].Columns["thanhtien"].DataType);
                Tbl.Columns.Add("tendonvitinh", Set.Tables[0].Columns["tendonvitinh"].DataType);
                Tbl.Columns.Add("Id", Set.Tables[0].Columns["Id"].DataType);
                foreach (DataRow Rw in Set.Tables[0].Rows)
                {
                    if (Rw["Dongiathucte"].ToString().Trim() == "" || Rw["Dongiathucte"].ToString().Trim() == "0")
                    {
                        NewTbl = PrcTblDm_NhapxuathanghoaById(int.Parse(Rw["Thucpham_thucanId"].ToString()), NgayNX, TrangthaiNX);
                        if (NewTbl.Rows.Count > 0)
                        {
                            Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], NewTbl.Rows[0]["Dongia"], float.Parse(Rw["Soluong"].ToString()) * float.Parse(NewTbl.Rows[0]["Dongia"].ToString()), Rw["tendonvitinh"], Rw["Id"]);
                        }
                        else
                        {
                            Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], 0, 0, Rw["tendonvitinh"], Rw["Id"]);
                        }
                    }
                    else
                    {

                        NewTbl = PrcTblDm_NhapxuathanghoaById(int.Parse(Rw["Thucpham_thucanId"].ToString()), NgayNX, TrangthaiNX);
                        if (NewTbl.Rows.Count > 0)
                        {
                            Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], NewTbl.Rows[0]["Dongia"], float.Parse(Rw["Soluong"].ToString()) * float.Parse(NewTbl.Rows[0]["Dongia"].ToString()), Rw["tendonvitinh"], Rw["Id"]);
                        }
                        else
                        {
                            Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], Rw["Dongiathucte"], float.Parse(Rw["Soluong"].ToString()) * float.Parse(Rw["Dongiathucte"].ToString()), Rw["tendonvitinh"], Rw["Id"]);
                        }
                    }

                }
                Reset.Tables.Add(Tbl);
                return Reset;
            }
            catch { return null; }
        }
        public DataSet TblLenthucphammonanchobuaByBaoanIdFillDongia_Thanhtien(int BaoanId, DateTime NgayNX, int TrangthaiNX)
        {
            DataSet Set = this.TblLenthucphammonanchobuaByBaoanId(BaoanId);
            DataTable NewTbl = new DataTable();
            DataColumn Thanhtien = new DataColumn();
            Thanhtien.ColumnName = Thanhtien.Caption = "Thanhtien";
            Thanhtien.DataType = System.Type.GetType("System.Double");
            Thanhtien.AllowDBNull = true;
            Set.Tables[0].Columns.Add(Thanhtien);
            DataSet Reset = new DataSet();
            DataTable Tbl = new DataTable();

            Tbl.Columns.Add("STT", Set.Tables[0].Columns["STT"].DataType);
            Tbl.Columns.Add("Thucpham_thucanId", Set.Tables[0].Columns["Thucpham_thucanId"].DataType);
            Tbl.Columns.Add("BaoanId", Set.Tables[0].Columns["BaoanId"].DataType);
            Tbl.Columns.Add("Tennguyenlieu", Set.Tables[0].Columns["Tennguyenlieu"].DataType);
            Tbl.Columns.Add("Soluong", Set.Tables[0].Columns["Soluong"].DataType);
            Tbl.Columns.Add("Dongia", Set.Tables[0].Columns["Dongia"].DataType);
            Tbl.Columns.Add("thanhtien", Set.Tables[0].Columns["thanhtien"].DataType);
            Tbl.Columns.Add("tendonvitinh", Set.Tables[0].Columns["tendonvitinh"].DataType);
            Tbl.Columns.Add("Id", Set.Tables[0].Columns["Id"].DataType);

            foreach (DataRow Rw in Set.Tables[0].Rows)
            {
                NewTbl = PrcTblDm_NhapxuathanghoaById(int.Parse(Rw["Thucpham_thucanId"].ToString()), NgayNX, TrangthaiNX);
                if (NewTbl.Rows.Count > 0)
                {
                    Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], NewTbl.Rows[0]["Dongia"], float.Parse(Rw["Soluong"].ToString()) * float.Parse(NewTbl.Rows[0]["Dongia"].ToString()), Rw["tendonvitinh"], Rw["Id"]);
                }
                else
                {
                    Tbl.Rows.Add(Rw["STT"], Rw["Thucpham_thucanId"], Rw["BaoanId"], Rw["Tennguyenlieu"], Rw["Soluong"], 0, 0, Rw["tendonvitinh"], Rw["Id"]);
                }
            }
            Reset.Tables.Add(Tbl);
            return Reset;

        }
    }
}
